﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;


namespace KPIS.GERP.GIMS.DAL
{
    public sealed class BidderQuotationDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public BidderQuotationDAL()
        {

        }

        public IList<BidderQuotationInfo> GetListBidderQuotation(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_bidder_seq", BidderSeq);

                strSQL = "select bq.* " +
                        " , coalesce(ri.icm_res_item_name, x.bgm_exp_name) as item_name " +
                        " , coalesce(u.icm_unit_name, 'รายการ') as unit_name " +
                        " from icm_bidder_quotation bq " +
                        " inner join icm_purchase_request_item pri on bq.icm_pr_item_seq = pri.icm_pr_item_seq " +
                        " inner join bgm_mas_expense x on pri.bgm_exp_seq = x.bgm_exp_seq " +
                        " left join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq " +
                        " left join icm_mas_unit u on ri.icm_unit_seq = u.icm_unit_seq " +
                        " where icm_bidder_seq = @icm_bidder_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_bidder_quo_seq"]);
                        infoBidder.Seq = Convert.ToInt32(dr[i]["icm_bidder_seq"]);
                        infoPritem.Seq = Convert.ToInt32(dr[i]["icm_pr_item_seq"]);
                        infoPritem.ItemName = dr[i]["item_name"].ToString();
                        infoItem.Name = dr[i]["item_name"].ToString();
                        infoUnit.Name = dr[i]["unit_name"].ToString();
                        info.Descr = dr[i]["item_descr"].ToString();
                        info.Qty = Convert.ToInt32(dr[i]["item_qty"].ToString());
                        info.Price = Convert.ToDouble(dr[i]["item_price"].ToString());
                        info.ExcludeVatAmount = Convert.ToDouble(dr[i]["exclude_vat_amount"].ToString());
                        info.VatAmount = Convert.ToDouble(dr[i]["vat_amount"].ToString());
                        info.DiscountPercent = (Convert.IsDBNull(dr[i]["discount_percent"])) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["discount_percent"].ToString());
                        info.ExtraDiscount = (Convert.IsDBNull(dr[i]["extra_discount"])) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["extra_discount"].ToString());
                        info.NetAmount = Convert.ToDouble(dr[i]["net_amount"].ToString());
                        info.Remarks = dr[i]["remarks"].ToString();

                        infoItem.InfoUnit = infoUnit;
                        infoPritem.InfoItem = infoItem;
                        info.InfoPrItem = infoPritem;
                        info.InfoBidder = infoBidder;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public BidderQuotationInfo GetBidderQuotation(int BidderQuoSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            BidderQuotationInfo info = new BidderQuotationInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_bidder_quo_seq", BidderQuoSeq);

                strSQL = "select bq.* " +
                        " , coalesce(ri.icm_res_item_name, x.bgm_exp_name) as item_name " +
                        " , coalesce(u.icm_unit_name, 'รายการ') as unit_name " +
                        " , v.icm_vat_seq" +
                        " , v.icm_vat_name" +
                        " from icm_bidder_quotation bq " +
                        " inner join icm_bidder b on bq.icm_bidder_seq = b.icm_bidder_seq" +
                        " inner join icm_purchase_request_item pri on bq.icm_pr_item_seq = pri.icm_pr_item_seq " +
                        " inner join bgm_mas_expense x on pri.bgm_exp_seq = x.bgm_exp_seq " +
                        " left join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq " +
                        " left join icm_mas_unit u on ri.icm_unit_seq = u.icm_unit_seq " +
                        " left join icm_sys_vat v on v.icm_vat_seq = b.icm_vat_seq " +
                        " where icm_bidder_quo_seq = @icm_bidder_quo_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    BidderInfo infoBidder = new BidderInfo();
                    PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();
                    ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                    MasterUnitInfo infoUnit = new MasterUnitInfo();
                    SysVatInfo infoVat = new SysVatInfo();

                    info.Seq = Convert.ToInt32(dr[0]["icm_bidder_quo_seq"]);
                    infoBidder.Seq = Convert.ToInt32(dr[0]["icm_bidder_seq"]);
                    infoVat.Seq = (Convert.IsDBNull(dr[0]["icm_vat_seq"])) ? null : (Nullable<int>)Convert.ToDouble(dr[0]["icm_vat_seq"].ToString());
                    infoVat.Name = dr[0]["icm_vat_name"].ToString();
                    infoPritem.Seq = Convert.ToInt32(dr[0]["icm_pr_item_seq"]);
                    infoPritem.ItemName = dr[0]["item_name"].ToString();
                    infoItem.Name = dr[0]["item_name"].ToString();
                    infoUnit.Name = dr[0]["unit_name"].ToString();
                    info.Descr = dr[0]["item_descr"].ToString();
                    info.Qty = Convert.ToInt32(dr[0]["item_qty"].ToString());
                    info.Price = Convert.ToDouble(dr[0]["item_price"].ToString());
                    info.ExcludeVatAmount = Convert.ToDouble(dr[0]["exclude_vat_amount"].ToString());
                    info.VatAmount = Convert.ToDouble(dr[0]["vat_amount"].ToString());
                    info.DiscountPercent = (Convert.IsDBNull(dr[0]["discount_percent"])) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["discount_percent"].ToString());
                    info.ExtraDiscount = (Convert.IsDBNull(dr[0]["extra_discount"])) ? null : (Nullable<double>)Convert.ToDouble(dr[0]["extra_discount"].ToString());
                    info.NetAmount = Convert.ToDouble(dr[0]["net_amount"].ToString());
                    info.Remarks = dr[0]["remarks"].ToString();

                    infoBidder.InfoVat = infoVat;
                    infoItem.InfoUnit = infoUnit;
                    infoPritem.InfoItem = infoItem;
                    info.InfoPrItem = infoPritem;
                    info.InfoBidder = infoBidder;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<BidderQuotationInfo> GetItemNameFromPurchaseRequest(int pr_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", pr_seq);

                strSQL = "SELECT DISTINCT pri.ICM_PR_ITEM_SEQ "
                        +" , CONCAT('<font color=ForestGreen><b>' "
	                    +" , COALESCE(ri.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME), cast( '</b></font>' as char) "
		                +" , COALESCE('</br>',pri.ICM_PR_ITEM_DESCR,''),cast(' </br>จำนวน <font color= Teal><b>' as char) "
		                +" , FORMAT(pri.ICM_PR_ITEM_QTY,0), cast( '</b></font> ' as char) "
		                +" , COALESCE(u.ICM_UNIT_NAME, ' รายการ'), cast(' </br>วงเงินขอ <font color=Teal><b>' as char) "
		                +" , FORMAT(pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE,2), cast('</b></font> บาท' as char) ) AS ITEM_DESCR  "
                        +" FROM ICM_BIDDER_QUOTATION q "
                        +" INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                        +" INNER JOIN ICM_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ  "
                        +" INNER JOIN BGM_MAS_EXPENSE x ON pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ  LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ  "
                        +" LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ   "
                        + " WHERE pri.ICM_PR_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();

                        infoPritem.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        infoPritem.Descr = dr[i]["ITEM_DESCR"].ToString();

                        info.InfoPrItem = infoPritem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<BidderQuotationInfo> GetItemNameFromBid(int bid_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@seq", bid_seq);

                strSQL = " SELECT DISTINCT pri.ICM_PR_ITEM_SEQ "
                         +" , CONCAT('<font color=ForestGreen><b>', COALESCE(ri.ICM_RES_ITEM_NAME, x.BGM_EXP_NAME) "
                         +" ,cast( '</b></font>' as char), COALESCE('</br>',pri.ICM_PR_ITEM_DESCR,'') "
                         +" ,cast(' </br>จำนวน <font color= Teal><b>' as char), FORMAT(pri.ICM_PR_ITEM_QTY,0), '</b></font> ', COALESCE(u.ICM_UNIT_NAME, ' รายการ') "
                         +" ,cast( ' </br>วงเงินขอ <font color=Teal><b>' as char), FORMAT(pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE,2), cast('</b></font> บาท' as char) "
                         + " ) AS ITEM_DESCR"
                         + " FROM ICM_BIDDER_QUOTATION q"
                         + " INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                         + " INNER JOIN ICM_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ "
                         + " INNER JOIN BGM_MAS_EXPENSE x ON pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                         + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ "
                         + " LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                         + " WHERE b.ICM_BID_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();

                        infoPritem.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        infoPritem.Descr = dr[i]["ITEM_DESCR"].ToString();

                        info.InfoPrItem = infoPritem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<BidderQuotationInfo> GetBidderQuotationFromPurchaseRequestItemWithPurchaseRequest(int pr_seq, int pr_item_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", pr_item_seq);
                dbManager.AddParameters(1, "@pr_seq", pr_seq);

                strSQL = " SELECT pri.ICM_PR_ITEM_SEQ "
                        +" , CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME  "
                        +" , b.VAT_PERCENT "
                        +" , v.ICM_VAT_SEQ "
                        +" , v.ICM_VAT_NAME "
                        +" , CONCAT(q.ITEM_DESCR "
                        +" , cast(' </br>จำนวน ' as char), FORMAT(q.ITEM_QTY,0), COALESCE(u.ICM_UNIT_NAME, ' รายการ') "
                        +" , cast(' </br>ราคา ' as char), FORMAT(q.EXCLUDE_VAT_AMOUNT + q.VAT_AMOUNT,2), ' บาท' "
                        +" , cast(' </br>ส่วนลด ' as char), FORMAT(q.EXCLUDE_VAT_AMOUNT + q.VAT_AMOUNT - q.NET_AMOUNT,2), ' บาท' "
                        +" , cast(' </br>รวมเป็น ' as char), FORMAT(q.NET_AMOUNT,2), ' บาท' "
                        +" ) AS QUO_DESCR "
                        +" , CONCAT(CAST(q.ICM_BIDDER_QUO_SEQ AS CHAR), ';', q.SELECTED, ';', FORMAT(q.NET_AMOUNT,2), ';' "
                        +" , FORMAT(coalesce((select min(q1.net_amount) "
                        +" from icm_bidder_quotation q1 "
                        +" inner join icm_bidder b1 on q1.icm_bidder_seq = b1.icm_bidder_seq "
                        +" where b1.icm_pr_seq = pri.ICM_PR_SEQ "
                        +" and q1.icm_pr_item_seq = q.ICM_PR_ITEM_SEQ "
                        +" ),0),2) "
                        +" ) AS KEY_DESCR "
                        +" FROM ICM_BIDDER_QUOTATION q "
                        +" INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                        +" INNER JOIN ICM_PURCHASE_REQUEST pr ON b.ICM_PR_SEQ = pr.ICM_PR_SEQ "
                        +" INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ  "
                        +" INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                        +" INNER JOIN ICM_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ  "
                        +" INNER JOIN BGM_MAS_EXPENSE x ON pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ  "
                        +" LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ  "
                        +" LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                        + " WHERE pri.ICM_PR_ITEM_SEQ = @seq"
                        + " AND pr.ICM_PR_SEQ = @pr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();
                        SysVatInfo infoVat = new SysVatInfo();

                        infoContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"])) ? null : (Nullable<double>)Convert.ToInt32(dr[i]["VAT_PERCENT"]);
                        infoPritem.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        info.Descr = dr[i]["QUO_DESCR"].ToString();
                        info.Remarks = dr[i]["KEY_DESCR"].ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["ICM_VAT_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();

                        infoBidder.InfoVat = infoVat;
                        infoBidder.InfoMasContact = infoContact;
                        info.InfoBidder = infoBidder;
                        info.InfoPrItem = infoPritem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<BidderQuotationInfo> GetBidderQuotationFromPurchaseRequestItemWithBid(int bid_seq, int pr_item_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@seq", pr_item_seq);
                dbManager.AddParameters(1, "@bid_seq", bid_seq);

                strSQL =  " SELECT pri.ICM_PR_ITEM_SEQ "
                         +" , CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME "
                         +" , b.VAT_PERCENT "
                         +" , v.ICM_VAT_SEQ "
                         +" , v.ICM_VAT_NAME "
                         +" , CONCAT(q.ITEM_DESCR "
                         +" , cast(' </br>จำนวน ' as char), FORMAT(q.ITEM_QTY,0 ), COALESCE(u.ICM_UNIT_NAME, cast( ' รายการ' as char) ) "
                         +" , cast( ' </br>ราคา ' as char), FORMAT(q.EXCLUDE_VAT_AMOUNT+ q.VAT_AMOUNT,2 ), cast(' บาท' as char ) "
                         +" , cast(' </br>ส่วนลด ' as char), FORMAT(q.EXCLUDE_VAT_AMOUNT+  q.VAT_AMOUNT - q.NET_AMOUNT,2), cast(' บาท' as char) "
                         +" , cast(' </br>รวมเป็น ' as char), FORMAT(q.NET_AMOUNT,2), cast( ' บาท' as char) "
                         +" ) AS QUO_DESCR "
                         +" , CONCAT(CAST(q.ICM_BIDDER_QUO_SEQ AS CHAR), ';', q.SELECTED, ';', FORMAT(q.NET_AMOUNT,2), ';' "
                         +" , FORMAT(coalesce((select min(q1.net_amount) "
                         +" from icm_bidder_quotation q1 "
                         +" inner join icm_bidder b1 on q1.icm_bidder_seq = b1.icm_bidder_seq "
                         +" where b1.icm_pr_seq = pri.ICM_PR_SEQ "
                         +" and q1.icm_pr_item_seq = q.ICM_PR_ITEM_SEQ "
                         +" ),0),2) "
                         +" ) AS KEY_DESCR "
                         +" FROM ICM_BIDDER_QUOTATION q "
                         +" INNER JOIN ICM_BIDDER b ON q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ "
                         +" INNER JOIN ICM_BID bi ON b.ICM_BID_SEQ = bi.ICM_BID_SEQ "
                         +" INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ "
                         +" INNER JOIN ICM_SYS_VAT v ON b.ICM_VAT_SEQ = v.ICM_VAT_SEQ "
                         +" INNER JOIN ICM_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ "
                         +" INNER JOIN BGM_MAS_EXPENSE x ON pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ "
                         +" LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ "
                         +" LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ "
                         + " WHERE pri.ICM_PR_ITEM_SEQ = @seq"
                         + " AND bi.ICM_BID_SEQ = @bid_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        PurchaseRequestItemInfo infoPritem = new PurchaseRequestItemInfo();
                        SysVatInfo infoVat = new SysVatInfo();

                        infoContact.Name_th = dr[i]["ICM_CON_NAME"].ToString();
                        infoBidder.VatPercent = (Convert.IsDBNull(dr[i]["VAT_PERCENT"])) ? null : (Nullable<double>)Convert.ToInt32(dr[i]["VAT_PERCENT"]);
                        infoPritem.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        info.Descr = dr[i]["QUO_DESCR"].ToString();
                        info.Remarks = dr[i]["KEY_DESCR"].ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["ICM_VAT_SEQ"])) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ICM_VAT_SEQ"]);
                        infoVat.Name = dr[i]["ICM_VAT_NAME"].ToString();

                        infoBidder.InfoVat = infoVat;
                        infoBidder.InfoMasContact = infoContact;
                        info.InfoBidder = infoBidder;
                        info.InfoPrItem = infoPritem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void RecalculateVat(int BidderSeq, int VatSeq, double VatPercent, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                dbManagerTrans.CreateParameters(3);

                dbManagerTrans.AddParameters(0, "@bidder_seq", BidderSeq);
                dbManagerTrans.AddParameters(1, "@vat_seq", VatSeq);
                dbManagerTrans.AddParameters(2, "@vat_percent", VatPercent);

                strSQL = "CALL P_Auto_Update_Bidder_Quotation(@bidder_seq, @vat_seq, @vat_percent)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
        }

        public JudgementInfo GetJudgement(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            JudgementInfo info = new JudgementInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_pr_seq", PrSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL =  " SELECT main.* "
                         +" , CAST(CASE WHEN BUDGET_AMOUNT > PURCHASE_AMOUNT "
                         +" THEN CONCAT(cast(' วงเงินจัดหาน้อยกว่าวงเงินงบประมาณเป็นจำนวน 'as char) ,  FORMAT(BUDGET_AMOUNT - PURCHASE_AMOUNT,2) ,cast( ' บาท' as char)) "
                         +" WHEN BUDGET_AMOUNT = PURCHASE_AMOUNT "
                         +" THEN ' วงเงินจัดหาเท่ากับวงเงินงบประมาณ' "
                         +" ELSE CONCAT(cast('วงเงินจัดหามากกว่าวงเงินงบประมาณเป็นจำนวน ' as char), FORMAT(PURCHASE_AMOUNT - BUDGET_AMOUNT,2),cast( ' บาท'  as char)) "
                         + " END AS CHAR) AS RESULT_NAME"
                         + " FROM "
                         + "(select sum((icm_pr_item_qty * icm_pr_item_price)) as budget_amount "
                         + " , (select sum(q.net_amount)"
                         + " from icm_bidder_quotation q"
                         + " inner join icm_purchase_request_item pri on q.icm_pr_item_seq = pri.icm_pr_item_seq"
                         + " where pri.icm_pr_seq = @icm_pr_seq"
                         + " and q.selected = @yes_status"
                         + " ) as purchase_amount "
                         + " from icm_purchase_request_item"
                         + " where icm_pr_seq = @icm_pr_seq"
                         + " ) main";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    info.BudgetAmount = (Convert.IsDBNull(dr[0]["budget_amount"]) ? null : (Nullable<double>)(dr[0]["budget_amount"]));
                    info.PurchaseAmount = (Convert.IsDBNull(dr[0]["purchase_amount"]) ? null : (Nullable<double>)(dr[0]["purchase_amount"]));
                    info.ResultName = dr[0]["result_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public JudgementInfo GetJudgementFromBid(int BidSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            JudgementInfo info = new JudgementInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@icm_bid_seq", BidSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT main.*"
                        + " , CAST(CASE WHEN BUDGET_AMOUNT > PURCHASE_AMOUNT"
                            + " THEN CONCAT(' วงเงินจัดหาน้อยกว่าวงเงินงบประมาณเป็นจำนวน ' ,  FORMAT(BUDGET_AMOUNT - PURCHASE_AMOUNT,2) , ' บาท')"
                            + " WHEN BUDGET_AMOUNT = PURCHASE_AMOUNT"
                            + " THEN ' วงเงินจัดหาเท่ากับวงเงินงบประมาณ' "
                            + " ELSE CONCAT('วงเงินจัดหามากกว่าวงเงินงบประมาณเป็นจำนวน ', FORMAT(PURCHASE_AMOUNT - BUDGET_AMOUNT,2), ' บาท')   "
                        + " END AS CHAR) AS RESULT_NAME"
                        + " FROM "
                        + "(select sum((icm_pr_item_qty * icm_pr_item_price)) as budget_amount "
                        + " , (select sum(q.net_amount)"
                            + " from icm_bidder_quotation q"
                                + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                            + " where b.icm_bid_seq = @icm_bid_seq"
                                + " and q.selected = @yes_status"
                        + " ) as purchase_amount "
                        + " from icm_purchase_request_item"
                        + " where icm_pr_seq in (select icm_pr_seq from icm_bid where icm_bid_seq = @icm_bid_seq)"
                        + " ) main";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    info.BudgetAmount = (Convert.IsDBNull(dr[0]["budget_amount"]) ? null : (Nullable<double>)(dr[0]["budget_amount"]));
                    info.PurchaseAmount = (Convert.IsDBNull(dr[0]["purchase_amount"]) ? null : (Nullable<double>)(dr[0]["purchase_amount"]));
                    info.ResultName = dr[0]["result_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<BidderQuotationInfo> GetListJudgement(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_pr_seq", PrSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "select q.icm_bidder_quo_seq "
                            + " ,cast(concat(coalesce(ri.icm_res_item_name, x.bgm_exp_name), ' จำนวน ', pri.icm_pr_item_qty, ' ', coalesce(u.icm_unit_name, 'รายการ'), ' วงเงินงบประมาณ ', format((pri.icm_pr_item_qty * pri.icm_pr_item_price),2) , ' บาท') as char) as root_item_descr "
                            + " , b.icm_con_seq "
                            + ", CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME "
                            + " , pri.icm_pr_item_seq "
                            + " , q.icm_bidder_seq "
                            + " , q.item_descr "
                            + " , coalesce(q.item_qty,0) as item_qty"
                            + " , coalesce(u.icm_unit_name, ' รายการ') as unit_name"
                            + " , coalesce(q.item_price,0) as item_price"
                            + " , coalesce(q.net_amount, 0) as net_amount"
                            + ", q.SELECTED_REASON"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM pri"
                            + " LEFT JOIN ICM_BIDDER_QUOTATION q ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ"
                            + " LEFT JOIN ICM_BIDDER b on q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " LEFT JOIN ICM_MAS_CONTACT c on b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " LEFT JOIN BGM_MAS_EXPENSE x on pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri on pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u on ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE pri.ICM_PR_SEQ = @icm_pr_seq"
                            + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();

                        info.Seq = (Convert.IsDBNull(dr[i]["icm_bidder_quo_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_bidder_quo_seq"]));
                        info.RootItemDescr = dr[i]["root_item_descr"].ToString();
                        infoMasContact.Seq = (Convert.IsDBNull(dr[i]["icm_con_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_con_seq"]));
                        infoMasContact.Name_th = dr[i]["icm_con_name"].ToString();
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["icm_pr_item_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_pr_item_seq"]));
                        infoBidder.Seq = (Convert.IsDBNull(dr[i]["icm_bidder_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_bidder_seq"]));
                        info.Descr = dr[i]["item_descr"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["item_qty"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["item_qty"]));
                        infoUnit.Name = dr[i]["unit_name"].ToString();
                        info.Price = (Convert.IsDBNull(dr[i]["item_price"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["item_price"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["net_amount"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["net_amount"]));
                        info.SelectedReason = dr[i]["SELECTED_REASON"].ToString();

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        info.InfoPrItem = infoPrItem;
                        infoBidder.InfoMasContact = infoMasContact;
                        info.InfoBidder = infoBidder;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<BidderQuotationInfo> GetListJudgementFromBid(int BidSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_bid_seq", BidSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "select q.icm_bidder_quo_seq "
                        + " , cast(concat(coalesce(ri.icm_res_item_name, x.bgm_exp_name), ' จำนวน ', pri.icm_pr_item_qty, ' ', coalesce(u.icm_unit_name, 'รายการ'), ' วงเงินงบประมาณ ', (pri.icm_pr_item_qty * pri.icm_pr_item_price) , ' บาท') as char) as root_item_descr "
                        + " , b.icm_con_seq "
                        + ", CONCAT(c.ICM_CON_NAME_TH, ' ',CASE WHEN LENGTH(c.ICM_CON_NAME_ENG) > 0 THEN CONCAT('(',c.ICM_CON_NAME_ENG,')') ELSE ' ' END) AS ICM_CON_NAME "
                        + " , pri.icm_pr_item_seq "
                        + " , q.icm_bidder_seq "
                        + " , q.item_descr "
                        + " , coalesce(q.item_qty,0) as item_qty"
                        + " , coalesce(u.icm_unit_name, ' รายการ') as unit_name"
                        + " , coalesce(q.item_price,0) as item_price"
                        + " , coalesce(q.net_amount, 0) as net_amount"
                        + ", q.SELECTED_REASON"
                    + " FROM ICM_PURCHASE_REQUEST_ITEM pri"
                        + " LEFT JOIN ICM_BIDDER_QUOTATION q ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ"
                        + " LEFT JOIN ICM_BIDDER b on q.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                        + " LEFT JOIN ICM_MAS_CONTACT c on b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                        + " LEFT JOIN BGM_MAS_EXPENSE x on pri.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                        + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri on pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                        + " LEFT JOIN ICM_MAS_UNIT u on ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                    + " WHERE pri.ICM_PR_SEQ IN (select icm_pr_seq from icm_bid where icm_bid_seq = @icm_bid_seq)"
                        + " AND b.ICM_BID_SEQ = @icm_bid_seq"
                        + " AND q.SELECTED = @yes_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterContactInfo infoMasContact = new MasterContactInfo();

                        info.Seq = (Convert.IsDBNull(dr[i]["icm_bidder_quo_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_bidder_quo_seq"]));
                        info.RootItemDescr = dr[i]["root_item_descr"].ToString();
                        infoMasContact.Seq = (Convert.IsDBNull(dr[i]["icm_con_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_con_seq"]));
                        infoMasContact.Name_th = dr[i]["icm_con_name"].ToString();
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["icm_pr_item_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_pr_item_seq"]));
                        infoBidder.Seq = (Convert.IsDBNull(dr[i]["icm_bidder_seq"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["icm_bidder_seq"]));
                        info.Descr = dr[i]["item_descr"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["item_qty"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["item_qty"]));
                        infoUnit.Name = dr[i]["unit_name"].ToString();
                        info.Price = (Convert.IsDBNull(dr[i]["item_price"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["item_price"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["net_amount"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["net_amount"]));
                        info.SelectedReason = dr[i]["SELECTED_REASON"].ToString();

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        info.InfoPrItem = infoPrItem;
                        infoBidder.InfoMasContact = infoMasContact;
                        info.InfoBidder = infoBidder;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(int BidderSeq, IList<BidderQuotationInfo> infoListBidderQuotation, IDBManager dbManagerTrans)
        {
            string strSql = "";
            try
            {
                for (int i = 0; i < infoListBidderQuotation.Count; i++)
                {
                    dbManagerTrans.CreateParameters(13);
                    dbManagerTrans.AddParameters(0, "@icm_bidder_seq", BidderSeq);
                    dbManagerTrans.AddParameters(1, "@icm_pr_item_seq", infoListBidderQuotation[i].InfoPrItem.Seq);
                    dbManagerTrans.AddParameters(2, "@item_descr", infoListBidderQuotation[i].Descr);
                    dbManagerTrans.AddParameters(3, "@item_qty", infoListBidderQuotation[i].Qty);
                    dbManagerTrans.AddParameters(4, "@item_price", infoListBidderQuotation[i].Price);
                    dbManagerTrans.AddParameters(5, "@exclude_vat_amount", infoListBidderQuotation[i].ExcludeVatAmount);
                    dbManagerTrans.AddParameters(6, "@vat_amount", infoListBidderQuotation[i].VatAmount);
                    dbManagerTrans.AddParameters(7, "@discount_percent", infoListBidderQuotation[i].DiscountPercent);
                    dbManagerTrans.AddParameters(8, "@extra_discount", infoListBidderQuotation[i].ExtraDiscount);
                    dbManagerTrans.AddParameters(9, "@net_amount", infoListBidderQuotation[i].NetAmount);
                    dbManagerTrans.AddParameters(10, "@remarks", infoListBidderQuotation[i].Remarks);
                    dbManagerTrans.AddParameters(11, "@created_by", infoListBidderQuotation[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(12, "@created_when", infoListBidderQuotation[i].InfoRecord.Created_when);

                    strSql = "INSERT INTO icm_bidder_quotation (icm_bidder_seq, icm_pr_item_seq, item_descr, item_qty, item_price" +
                             ", exclude_vat_amount, vat_amount, discount_percent, extra_discount, net_amount, created_by, created_when)" +
                             " VALUES ( @icm_bidder_seq, @icm_pr_item_seq, @item_descr, @item_qty, @item_price, @exclude_vat_amount" +
                             ", @vat_amount, @discount_percent, @extra_discount, @net_amount, @created_by, @created_when)";
                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Insert(BidderQuotationInfo infoBidderQuotation)
        {
            string strSql = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@icm_bidder_seq", infoBidderQuotation.InfoBidder.Seq);
                dbManager.AddParameters(1, "@icm_pr_item_seq", infoBidderQuotation.InfoPrItem.Seq);
                dbManager.AddParameters(2, "@item_descr", infoBidderQuotation.Descr);
                dbManager.AddParameters(3, "@item_qty", infoBidderQuotation.Qty);
                dbManager.AddParameters(4, "@item_price", infoBidderQuotation.Price);
                dbManager.AddParameters(5, "@exclude_vat_amount", infoBidderQuotation.ExcludeVatAmount);
                dbManager.AddParameters(6, "@vat_amount", infoBidderQuotation.VatAmount);
                dbManager.AddParameters(7, "@discount_percent", infoBidderQuotation.DiscountPercent);
                dbManager.AddParameters(8, "@extra_discount", infoBidderQuotation.ExtraDiscount);
                dbManager.AddParameters(9, "@net_amount", infoBidderQuotation.NetAmount);
                dbManager.AddParameters(10, "@remarks", infoBidderQuotation.Remarks);
                dbManager.AddParameters(11, "@created_by", infoBidderQuotation.InfoRecord.Created_by);
                dbManager.AddParameters(12, "@created_when", infoBidderQuotation.InfoRecord.Created_when);

                strSql = "INSERT INTO icm_bidder_quotation (icm_bidder_seq, icm_pr_item_seq, item_descr, item_qty, item_price" +
                         ", exclude_vat_amount, vat_amount, discount_percent, extra_discount, net_amount, remarks, created_by, created_when)" +
                         " VALUES ( @icm_bidder_seq, @icm_pr_item_seq, @item_descr, @item_qty, @item_price, @exclude_vat_amount" +
                         ", @vat_amount, @discount_percent, @extra_discount, @net_amount, @remarks, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertBid(BidderQuotationInfo infoBidderQuotation)
        {
            string strSql = "";
            BidderDAL dalBidder = new BidderDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@icm_bidder_seq", infoBidderQuotation.InfoBidder.Seq);
                dbManager.AddParameters(1, "@icm_pr_item_seq", infoBidderQuotation.InfoPrItem.Seq);
                dbManager.AddParameters(2, "@item_descr", infoBidderQuotation.Descr);
                dbManager.AddParameters(3, "@item_qty", infoBidderQuotation.Qty);
                dbManager.AddParameters(4, "@item_price", infoBidderQuotation.Price);
                dbManager.AddParameters(5, "@exclude_vat_amount", infoBidderQuotation.ExcludeVatAmount);
                dbManager.AddParameters(6, "@vat_amount", infoBidderQuotation.VatAmount);
                dbManager.AddParameters(7, "@discount_percent", infoBidderQuotation.DiscountPercent);
                dbManager.AddParameters(8, "@extra_discount", infoBidderQuotation.ExtraDiscount);
                dbManager.AddParameters(9, "@net_amount", infoBidderQuotation.NetAmount);
                dbManager.AddParameters(10, "@remarks", infoBidderQuotation.Remarks);
                dbManager.AddParameters(11, "@created_by", infoBidderQuotation.InfoRecord.Created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Now);

                strSql = "INSERT INTO icm_bidder_quotation (icm_bidder_seq, icm_pr_item_seq, item_descr, item_qty, item_price" +
                         ", exclude_vat_amount, vat_amount, discount_percent, extra_discount, net_amount, remarks, created_by, created_when)" +
                         " VALUES ( @icm_bidder_seq, @icm_pr_item_seq, @item_descr, @item_qty, @item_price, @exclude_vat_amount" +
                         ", @vat_amount, @discount_percent, @extra_discount, @net_amount, @remarks, @created_by, @created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
                dalBidder.UpdateWinnerStatus(Convert.ToInt32(infoBidderQuotation.InfoBidder.Seq), dbManager);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(BidderQuotationInfo infoBidderQuotation)
        {
            string strSql = "";
            try
            {
                dbManager.Open();
                if (infoBidderQuotation.InfoPrItem.Seq != null)
                {
                    dbManager.CreateParameters(13);
                    dbManager.AddParameters(0, "@icm_bidder_quo_seq", infoBidderQuotation.Seq);
                    dbManager.AddParameters(1, "@icm_pr_item_seq", infoBidderQuotation.InfoPrItem.Seq);
                    dbManager.AddParameters(2, "@item_descr", infoBidderQuotation.Descr);
                    dbManager.AddParameters(3, "@item_qty", infoBidderQuotation.Qty);
                    dbManager.AddParameters(4, "@item_price", infoBidderQuotation.Price);
                    dbManager.AddParameters(5, "@exclude_vat_amount", infoBidderQuotation.ExcludeVatAmount);
                    dbManager.AddParameters(6, "@vat_amount", infoBidderQuotation.VatAmount);
                    dbManager.AddParameters(7, "@discount_percent", infoBidderQuotation.DiscountPercent);
                    dbManager.AddParameters(8, "@extra_discount", infoBidderQuotation.ExtraDiscount);
                    dbManager.AddParameters(9, "@net_amount", infoBidderQuotation.NetAmount);
                    dbManager.AddParameters(10, "@remarks", infoBidderQuotation.Remarks);
                    dbManager.AddParameters(11, "@updated_by", infoBidderQuotation.InfoRecord.Updated_by);
                    dbManager.AddParameters(12, "@updated_when", DateTime.Now);

                    strSql = "UPDATE icm_bidder_quotation SET icm_pr_item_seq = @icm_pr_item_seq " +
                            " , item_descr = @item_descr " +
                            " , item_qty = @item_qty " +
                            " , item_price = @item_price " +
                            " , exclude_vat_amount = @exclude_vat_amount " +
                            " , vat_amount = @vat_amount " +
                            " , discount_percent = @discount_percent " +
                            " , extra_discount = @extra_discount " +
                            " , net_amount = @net_amount " +
                            " , remarks = @remarks " +
                            " , updated_by = @updated_by " +
                            " , updated_when = @updated_when " +
                            " WHERE icm_bidder_quo_seq = @icm_bidder_quo_seq";
                }
                else
                {
                    dbManager.CreateParameters(12);
                    dbManager.AddParameters(0, "@icm_bidder_quo_seq", infoBidderQuotation.Seq);
                    dbManager.AddParameters(1, "@item_descr", infoBidderQuotation.Descr);
                    dbManager.AddParameters(2, "@item_qty", infoBidderQuotation.Qty);
                    dbManager.AddParameters(3, "@item_price", infoBidderQuotation.Price);
                    dbManager.AddParameters(4, "@exclude_vat_amount", infoBidderQuotation.ExcludeVatAmount);
                    dbManager.AddParameters(5, "@vat_amount", infoBidderQuotation.VatAmount);
                    dbManager.AddParameters(6, "@discount_percent", infoBidderQuotation.DiscountPercent);
                    dbManager.AddParameters(7, "@extra_discount", infoBidderQuotation.ExtraDiscount);
                    dbManager.AddParameters(8, "@net_amount", infoBidderQuotation.NetAmount);
                    dbManager.AddParameters(9, "@remarks", infoBidderQuotation.Remarks);
                    dbManager.AddParameters(10, "@updated_by", infoBidderQuotation.InfoRecord.Updated_by);
                    dbManager.AddParameters(11, "@updated_when", DateTime.Now);

                    strSql = "UPDATE icm_bidder_quotation" +
                        " SET item_descr = @item_descr " +
                            " , item_qty = @item_qty " +
                            " , item_price = @item_price " +
                            " , exclude_vat_amount = @exclude_vat_amount " +
                            " , vat_amount = @vat_amount " +
                            " , discount_percent = @discount_percent " +
                            " , extra_discount = @extra_discount " +
                            " , net_amount = @net_amount " +
                            " , remarks = @remarks " +
                            " , updated_by = @updated_by " +
                            " , updated_when = @updated_when " +
                            " WHERE icm_bidder_quo_seq = @icm_bidder_quo_seq";
                }
                dbManager.ExecuteNonQuery(CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<BidderQuotationInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_BIDDER_QUOTATION "
                    + " WHERE ICM_BIDDER_QUO_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteBidFromDB(IList<BidderQuotationInfo> infoList, int bidder_seq)
        {
            string strSQL;
            string seq = "";
            BidderDAL dalBidder = new BidderDAL();
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_BIDDER_QUOTATION "
                    + " WHERE ICM_BIDDER_QUO_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dalBidder.UpdateWinnerStatus(bidder_seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<BidderQuotationInfo> GetBidderQuotationFromPurchaseRequestItem(int pr_seq, int vat_seq, Nullable<double> vat_percent, Nullable<int> bidder_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<BidderQuotationInfo> infoList = new List<BidderQuotationInfo>();
            int nCount = 0;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@pr_seq", pr_seq);
                dbManager.AddParameters(1, "@vat_seq", vat_seq);
                dbManager.AddParameters(2, "@vat_percent", (vat_percent.ToString().Length > 0 ? vat_percent : 0));
                dbManager.AddParameters(3, "@bidder_seq", bidder_seq);

                strSQL = "SELECT pri.ICM_PR_ITEM_SEQ, pri.ICM_PR_ITEM_QTY"//, bg.ICM_BIDDER_QUO_SEQ, bg.ITEM_DESCR"
                            + ", CASE WHEN @vat_seq = 1"
                                + " THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) / (pri.ICM_PR_ITEM_QTY + ((@vat_percent * pri.ICM_PR_ITEM_QTY) / 100))),2)"
                                + " ELSE pri.ICM_PR_ITEM_PRICE "
                            + " END AS ITEM_PRICE"
                            + ", pri.ICM_PR_ITEM_DESCR"
                            + " , CASE WHEN @vat_seq = 1 THEN ROUND(((((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                    + " WHEN @vat_seq = 2 THEN ROUND(((((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                + " ELSE (pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)"
                            + " END AS EXCLUDE_VAT_AMOUNT"
                            + " , CASE WHEN @vat_seq = 1 THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) - (((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                    + " WHEN @vat_seq = 2 THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) - (((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                + " ELSE 0"
                            + " END AS VAT_AMOUNT"
                            + ", (pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) AS NET_AMOUNT"
                            + ", COALESCE(ri.ICM_RES_ITEM_NAME, ex.BGM_EXP_NAME) AS ITEM_NAME"
                            + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM pri"
                            + " LEFT JOIN BGM_MAS_EXPENSE ex ON pri.BGM_EXP_SEQ = ex.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                    //+ " LEFT JOIN ICM_BID b ON pri.ICM_PR_SEQ = b.ICM_PR_SEQ"
                    //+ " LEFT JOIN ICM_BIDDER bd ON pri.ICM_PR_SEQ = bd.ICM_PR_SEQ"
                    //+ " LEFT JOIN ICM_BIDDER_QUOTATION bg ON pri.ICM_PR_ITEM_SEQ = bg.ICM_PR_ITEM_SEQ"
                        + " WHERE pri.ICM_PR_SEQ = @pr_seq";
                        //+ (bidder_seq.HasValue ? " AND bd.ICM_BIDDER_SEQ = @bidder_seq" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        BidderQuotationInfo info = new BidderQuotationInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        nCount++;

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);// (Convert.IsDBNull(dr[i]["ICM_BIDDER_QUO_SEQ"]) ? nCount : (Nullable<int>)(dr[i]["ICM_BIDDER_QUO_SEQ"]));//nCount;
                        infoPrItem.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        infoPrItem.ItemName = dr[i]["ITEM_NAME"].ToString();
                        infoUnit.Name = dr[i]["ICM_UNIT_NAME"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PR_ITEM_QTY"]));
                        info.Price = (Convert.IsDBNull(dr[i]["ITEM_PRICE"]) ? null : (Nullable<double>)(dr[i]["ITEM_PRICE"]));
                        info.Descr = dr[i]["ICM_PR_ITEM_DESCR"].ToString();
                        info.ExcludeVatAmount = (Convert.IsDBNull(dr[i]["EXCLUDE_VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["EXCLUDE_VAT_AMOUNT"]));
                        info.VatAmount = (Convert.IsDBNull(dr[i]["VAT_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["VAT_AMOUNT"]));
                        info.NetAmount = (Convert.IsDBNull(dr[i]["NET_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["NET_AMOUNT"]));
                        info.DiscountPercent = 0;
                        info.ExtraDiscount = 0;
                        infoRecord.Created_by = 1;
                        infoRecord.Created_when = DateTime.Now;

                        infoItem.InfoUnit = infoUnit;
                        infoPrItem.InfoItem = infoItem;
                        info.InfoPrItem = infoPrItem;
                        info.InfoRecord = infoRecord;

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertBidderQuotationFromBid(IList<PurchaseRequestItemInfo> infoList, int bidder_seq, int vat_seq, Nullable<double> vat_percent)
        {
            string strSQL;
            string seq = "";
            BidderDAL dalBidder = new BidderDAL();
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@bidder_seq", bidder_seq);
                dbManager.AddParameters(1, "@vat_seq", vat_seq);
                dbManager.AddParameters(2, "@vat_percent", (vat_percent.ToString().Length > 0 ? vat_percent : 0));
                dbManager.AddParameters(3, "@created_by", infoList[0].InfoRecord.Created_by);
                dbManager.AddParameters(4, "@created_when", infoList[0].InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_BIDDER_QUOTATION (ICM_BIDDER_SEQ, ICM_PR_ITEM_SEQ, ITEM_DESCR, ITEM_QTY, ITEM_PRICE"
                        + ", EXCLUDE_VAT_AMOUNT, VAT_AMOUNT, DISCOUNT_PERCENT, EXTRA_DISCOUNT, NET_AMOUNT, CREATED_BY, CREATED_WHEN)"
                        + " (SELECT @bidder_seq"
                            + ", pri.ICM_PR_ITEM_SEQ"
                            + ", pri.ICM_PR_ITEM_DESCR"
                            + ", pri.ICM_PR_ITEM_QTY"
                            + ", CASE WHEN @vat_seq = 1"
                                + " THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) / (pri.ICM_PR_ITEM_QTY + ((@vat_percent * pri.ICM_PR_ITEM_QTY) / 100))),2)"
                                + " ELSE pri.ICM_PR_ITEM_PRICE "
                            + " END AS ITEM_PRICE"
                            + " , CASE WHEN @vat_seq = 1 THEN ROUND(((((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                    + " WHEN @vat_seq = 2 THEN ROUND(((((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                + " ELSE (pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)"
                            + " END AS EXCLUDE_VAT_AMOUNT"
                            + " , CASE WHEN @vat_seq = 1 THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) - (((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                    + " WHEN @vat_seq = 2 THEN ROUND(((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) - (((pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE)/(100 + @vat_percent)) * 100)),2)"
                                + " ELSE 0"
                            + " END AS VAT_AMOUNT"
                            + ", 0"
                            + ", 0"
                            + ", (pri.ICM_PR_ITEM_QTY * pri.ICM_PR_ITEM_PRICE) AS NET_AMOUNT"
                            + ", @created_by"
                            + ", @created_when"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM pri"
                            + " LEFT JOIN BGM_MAS_EXPENSE ex ON pri.BGM_EXP_SEQ = ex.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                        + " WHERE pri.ICM_PR_ITEM_SEQ IN (" + seq + ")"
                        + " )";

                dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dalBidder.UpdateWinnerStatus(bidder_seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void SelectedBidderQuotation(IList<BidderQuotationInfo> infoList)
        {
            string strSQL = "";
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@selected", infoList[0].Selected);
                dbManager.AddParameters(1, "@reason", infoList[0].SelectedReason);
                dbManager.AddParameters(2, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_BIDDER_QUOTATION"
                    + " SET SELECTED = @selected"
                    + ", SELECTED_REASON = @reason"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_BIDDER_QUO_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
